''---------Insertion Into State_Master--------------------
rsSearch2.Open "select state from state_master where state='" & StringFormat(state1.Text) & "' and country_id in (select country_id from country_master where country='" & Trim(country1.Text) & "')", con, adLockOptimistic, adOpenKeyset
If rsSearch2.RecordCount = 0 Then
rs2.Open "select Max(state_id) from state_master", con, adLockOptimistic, adOpenKeyset
Dim stateID As Integer
stateID = rs2.Fields(0).Value
If stateID = 0 Then
stateID = 1
Else
stateID = stateID + 1
End If
rs_2.Open "select country_id from country_master where country='" & StringFormat(country1) & "'", con, adLockOptimistic, adOpenKeyset
''--------------Insertion into City_master----------------
rsSearch3.Open "select city from city_master where city='" & StringFormat(city1.Text) & "' and state_id in(select state_id from state_master where state='" & StringFormat(state1.Text) & "' and country_id in(select country_id from country_master where country='" & StringFormat(country1.Text) & "'))", con, adLockOptimistic, adOpenKeyset
If rsSearch3.RecordCount = 0 Then
Dim stateID2, cityID As Integer
rs3.Open "select state_id from state_master where state='" & StringFormat(state1.Text) & "' and country_id in(select country_id from country_master where country='" & StringFormat(country1.Text) & "')", con, adLockOptimistic, adOpenKeyset
stateID2 = rs3.Fields(0).Value
rs_31.Open "select Max(city_id) from city_master ", con, adLockOptimistic, adOpenKeyset
'----------------selection of city id'------------------------------------------------------------
Dim rsSearchcityID As Recordset
Set rsSearchcityID = New Recordset
rsSearchcityID.Open "select city_id from city_master where city='" & StringFormat(city1.Text) & "' and state_id in(select state_id from state_master where state='" & StringFormat(state1.Text) & "' and country_id in(select country_id from country_master where country='" & StringFormat(country1.Text) & "'))", con, adLockOptimistic, adOpenKeyset
'---for setting of country state and city in a single click of CityCombo-------------
Public Sub select_City(City_C As ComboBox, State_C As ComboBox, Country_C As ComboBox)
Dim i As Integer
Dim rsState, rsCountry As Recordset
Set rsState = New Recordset
rsState.Open "select state from state_master where state_id in (select state_id from city_master where city='" & StringFormat(City_C.Text) & "')", con, adLockOptimistic, adOpenKeyset
rsCountry.Open "select country from country_master where country_id in (select country_id from state_master where state='" & StringFormat(State_C.Text) & "')", con, adLockOptimistic, adOpenKeyset
If rsCountry.RecordCount = 0 Then
Else
Country_C.Clear
If rsCountry.RecordCount > 1 Then
While Not rsCountry.EOF
Combostate.List(i) = rsCountry.Fields(0).Value
If i = 0 Then 'for selecting first record
Value = rsCountry.Fields(0).Value
End If
i = i + 1
rsCountry.MoveNext
Wend
Country_C.Text = Value
Else
Country_C.Text = rsCountry.Fields(0).Value
End If
End If
End Sub
Public Function ID_Generator(ByVal Table As String, ByVal fieldname As String, ByVal prefix As String) As String
Dim rs1 As Recordset
Set rs1 = New Recordset
rs1.Open "SElect * from " & Table & " where company_Id=" & Global_CompanyId & "", con, adOpenKeyset, adLockOptimistic